package createBean;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.net.URL;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import freemarker.template.Configuration;
import freemarker.template.Template;
import freemarker.template.TemplateException;

/**
 * 处理Excel
 *
 */
public class ReadExcel {

	private static List<String> keys = new ArrayList<>();

	private static Map<String, String> mapTables = new HashMap<String, String>();

	private static Map<String, Integer> mapTitle = new HashMap<String, Integer>();// 表首字符缓存

	private final static String TEMPLATE_NAME = "templateService.ftl";

	private static String path = "";

	private static String bm = "";// 部门缩写
	private static String xt = "";// 系统缩写
	private static String bmxt = "";// 类路径后部分

	private static Map<String, String> mapTb = new HashMap<String, String>();// 存表中英文对应关系

	/**
	 * 需要读取的文件位置
	 */
	// private static String filePath = "C:\\Users\\hainan\\Desktop\\1";
	private static String filePath = "C:\\Users\\hainan\\Desktop\\采集情况\\省旅游委";

	/**
	 * 文档输出位置
	 */
	private static String fileOutPath = "C:\\Users\\hainan\\Desktop\\表名映射\\信息资源-表名映射.xlsx";

	private static int kg = 1;// 0: 新增生成, 1.重新生成实体类, 2: 直接返回, 不生成

	static {
		URL u = Thread.currentThread().getContextClassLoader().getResource("");
		File f = new File(u.toString());
		path = f.getParentFile().getParent().substring(6);
	}

	public static void main(String[] args) throws IOException, InterruptedException {
		// File file = new File("C:\\Users\\hainan\\git\\ceshi\\haishi\\file");
		File file = new File(filePath);
		String path = file.getPath();
		String[] list = file.list();
		for (String s : list) {
			String prefix = s.substring(s.lastIndexOf(".") + 1); // 获取后缀名
			if ("xlsx".equals(prefix)) {
				// 获得表名的前部分
				getStartTable(s);

				readXlsx(path + "\\" + s);

				// 创建实体类
				for (String key : keys) {
					String val = mapTables.get(key);
					createJavaBean(val, key);
				}

				// 创建Excel保存表名
				saveTableName(s);

				keys.clear();// 清除一个文件
				mapTables.clear();// 清除一个文件
				mapTitle.clear();
				mapTb.clear();
			}

		}

	}

	// 获得表名的前部分(并查询出表中对应的code码)
	private static void getStartTable(String s) {
		String[] s1 = s.split("\\.");
		String name = s1[0];
		String[] nm = name.split("_");
		String nm0 = nm[0];

		// 查询code码
		try {
			String sql1 = "select * from dept_code where deptname = \"" + nm0 + "\" limit 1";// 查询厅局码
			String c1 = MysqlDemo.getCode(sql1);// 查询厅局码

			String sql2 = "select * from sysinfo where name = \"" + name + "\" limit 1";// 查询系统码
			String c2 = MysqlDemo.getCode(sql2);// 查询系统码

			bm = c1.substring(0, 1).toUpperCase() + c1.substring(1).toLowerCase();// 查询厅局码
			xt = c2.substring(0, 1).toUpperCase() + c2.substring(1).toLowerCase();// 查询系统码
			bmxt = "." + c1.toLowerCase() + "." + c2.toLowerCase();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		// String b = PinyinUtils.getAlpha(s1[0]);
		// String[] sp = b.split("_");
		//
		// bm = sp[0].substring(0, 1).toUpperCase() + sp[0].substring(1).toLowerCase();
		// xt = sp[1].substring(0, 1).toUpperCase() + sp[1].substring(1).toLowerCase();
		// bmxt = "." + sp[0].toLowerCase() + "." + sp[1].toLowerCase();
	}

	public static void readXlsx(String path) throws IOException {
		File file = new File(path);
		FileInputStream is = new FileInputStream(file);
		readExcelTitleBySen(is);
	}

	/**
	 * 读取Excel表格表头的内容excel2007
	 * 
	 * @param is
	 * @return
	 * @throws IOException
	 */
	public static void readExcelTitleBySen(InputStream is) throws IOException {
		String table = "";// 表名
		String field = "";// 字段集(逗号隔开)

		@SuppressWarnings("resource")
		XSSFWorkbook wb = new XSSFWorkbook(is);
		XSSFSheet sheet = wb.getSheetAt(1);

		int num = sheet.getLastRowNum();
		// 获取表数据
		for (int i = 2; i <= num; i++) {
			XSSFRow row = sheet.getRow(i);
			XSSFCell c1 = row.getCell(0);
			String t = c1.getStringCellValue().replace("\r", "").replace("\n", "");
			t = replaceSpecStr(t);// 去除特殊字符

			if (t != null && t.trim().length() > 0) {// 将表字段放入map
				if (i > 2) {
					field = field.substring(2);
					mapTables.put(table, field);
					keys.add(table);
					field = "";// 清空, 放下一个表
					table = t;
				} else {
					table = t;
				}
			}

			String cnt = "";// 列名中文 + 列名英文 + "#" + 类型
			XSSFCell c01 = row.getCell(1);
			String cel01 = c01.getStringCellValue();
			cel01 = replaceSpecStr(cel01);// 去除特殊字符, 列名中文 
			if (cel01 == null) {
				field = field.substring(2);
				mapTables.put(table, field);
				keys.add(table);
				return;
			}
			XSSFCell c02 = row.getCell(2);
			String cel02 = c02.getStringCellValue();
			cel02 = replaceSpecStr(cel02);// 去除特殊字符, 列名英文
			if(cel02 == null) {//如果没有英文列名, 则直接翻译中文列名
				cel02 = PinyinUtils.getAlpha(cel01);
			}
			
			XSSFCell c03 = row.getCell(3);
			String cel03 = c03.getStringCellValue();
			cel03 = replaceSpecStr(cel03);// 去除特殊字符, 类型
			if(cel03 == null) {//如果没有类型, 则默认为char类型
				cel03 = "CHAR";
			}

			cnt = cel01 + "#" + cel02 + "#" + cel03;

			// cel = MyStringEscapeUtils.escapeSQL(cel);//去除特殊字符
			field = field + ",," + cnt;
		}

		// 将最后一组添加到map
		field = field.substring(2);
		mapTables.put(table, field);
		keys.add(table);
	}

	private static void createJavaBean(String val, String key) {

		Map<String, Integer> mapNum = new HashMap<>();

		// 生成的java类位置
		String pac = "package com.sefonsoft.dataanalysis.model" + bmxt + ";";// 配
		// 实体类中的标签导入位置
		String imp = "import com.sefonsoft.dataanalysis.annotation.";// 配

		// String[] a = {"你好", "我好", "nh", "你好", "你好"};//由文档解析出来
		String[] vals = val.split(",,");// 由文档解析出来

		List<String> lista = Arrays.asList(vals);

		// 获取标题
		// String title = "啊我的新闻文章哈";//由文档解析出来
		String title = key;// 由文档解析出来

		String t = PinyinUtils.getAlpha(title);

		t = t.substring(0, 1).toUpperCase() + t.substring(1).toLowerCase();

		// 处理表名重复
		Integer j = mapTitle.get(t);
		if (j != null) {
			mapTitle.put(t, ++j);
			t = t + j;
		} else {
			mapTitle.put(t, 0);
		}

		String tt = bm + "_" + xt + "_" + t;// 表名
		tt = tt.toLowerCase();// 变小写
		String tBean = bm + xt + t + "Bean";// 实体类名

		// 判断实体类是否重复
		String bName = "com.sefonsoft.dataanalysis.model" + bmxt + "." + tBean;// 配
		int beanNum = BeanNum(bName, bName, 0);
		if (beanNum > 0) {
			if (kg == 0) {// 新增
				tBean = tBean + beanNum;
				tt = tt + beanNum;// 表名
			} else if (kg == 2) {// 直接返回, 不生成
				return;
			}
			// kg == 1: 重新生成实体类
		}

		mapTb.put(key, tt);// 中英文对应关系

		List<String> list = new ArrayList<>();
		List<String> listEnd = new ArrayList<>();

		list.add(pac);
		list.add("");
		list.add(imp + "FieldMeta;");
		list.add(imp + "Table;");
		list.add(imp + "Id;");
		list.add("");

		list.add("/**");
		list.add(" * " + title);// 动态
		list.add(" * @Date " + getNowTime());// 动态
		list.add(" */");
		list.add("@Table(desc=\"" + title + "\", name = \"" + tt + "\", sysId=\"" + (bm + "_" + xt).toLowerCase()
				+ "\", fileKind=\"" + t.toLowerCase() + "\")");// 动态
		list.add("@Id(name = \"id\")");
		list.add("public class " + tBean + " {");// TestBean
		list.add("");

		/**
		 * 下边代码不动
		 */
		list.add("	/**\r\n" + "	 * id\r\n" + "	 */\r\n" + "	private String id;");
		list.add("");

		// 动态字段
		for (String str : lista) {
			String[] spl = str.split("#");
			String c0 = spl[0];// 列名(中文)
			String c1 = spl[1];// 列名(英文)
			String c2 = spl[2];// 类型

			// 转换成字母(大写)
			// String s = PinyinUtils.getAlpha(spl[1]).toUpperCase();
			String s = c1.toLowerCase();

			if(isNumeric(s)) {//如果是数字开头则以  "a" 开头
				s = "a" + s;
			}
			if ("id".equals(s)) {// 避免与 id 重复
				s = s + 1;
			}

			Integer i = mapNum.get(s);
			if (i != null) {
				mapNum.put(s, ++i);
				s = s + i;
			} else {
				mapNum.put(s, 0);
			}

			switch (c2) {//处理字段类型
			case "CHAR":
				list.add("	@FieldMeta(name = \"" + c0 + "\",type=\"varchar\",length=256)");
				break;
			case "TEXT":
				list.add("	@FieldMeta(name = \"" + c0 + "\",type=\"text\")");
				break;

			default:
				list.add("	@FieldMeta(name = \"" + c0 + "\",type=\"varchar\",length=256)");
				break;
			}

			list.add("	private String " + s + ";");
			list.add("");

			String s1 = s.substring(0, 1).toUpperCase() + s.substring(1);

			listEnd.add("	public String get" + s1 + "() {");
			listEnd.add("		return " + s + ";");
			listEnd.add("	}");
			listEnd.add("");

			listEnd.add("	public void set" + s1 + "(String " + s + ") {");
			listEnd.add("		this." + s + " = " + s + ";");
			listEnd.add("	}");
			listEnd.add("");
		}

		list.add("	public String getId() {\r\n" + "		return id;\r\n" + "	}\r\n" + "\r\n"
				+ "	public void setId(String id) {\r\n" + "		this.id = id;\r\n" + "	}");
		list.add("");

		// 将get/set添加到尾部
		list.addAll(listEnd);

		list.add("}");

		// 生成文件
		creatFile(list, tBean);

		// 生成serveice
		try {
			creatFileService(tBean, title);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 创建Excel保存表名
	private static void saveTableName(String s) throws IOException {
		// File f1 = new File(filePath + "\\中英文表名");
		// if(!f1.exists()) {
		// f1.mkdir();//创建文件夹
		// }
		//
		// FileOutputStream fos = new FileOutputStream(filePath + "\\中英文表名\\" + s);
		//
		// @SuppressWarnings("resource")
		// XSSFWorkbook wb = new XSSFWorkbook();
		// XSSFSheet cs = wb.createSheet("中英文表名");
		//
		// XSSFRow row = cs.createRow(0);
		// row.createCell(0).setCellValue("中文表名");
		// row.createCell(1).setCellValue("英文表名");

		String[] ss = s.split("\\.")[0].split("_");

		// 在已存在的文档添加数据
		File file = new File(fileOutPath);
		FileInputStream is = new FileInputStream(file);

		@SuppressWarnings("resource")
		XSSFWorkbook wb = new XSSFWorkbook(is);
		XSSFSheet cs = wb.getSheetAt(0);

		int lrn = cs.getLastRowNum();
		int size = keys.size();// 个数
		
		String nowTime = getNowTime();

		int i = 1;
		for (String tb : keys) {
			XSSFRow r = cs.createRow(lrn + i++);
			r.createCell(0).setCellValue(ss[0]);// 厅局
			r.createCell(1).setCellValue(ss[1]);// 系统
			r.createCell(2).setCellValue(tb);// 中文表名
			r.createCell(3).setCellValue(mapTb.get(tb));// 英文表名
			r.createCell(4).setCellValue(nowTime);// 生成时间
		}
		try {
			cs.addMergedRegion(new CellRangeAddress(lrn + 1, lrn + size, 0, 0));// 合并的单元格
			cs.addMergedRegion(new CellRangeAddress(lrn + 1, lrn + size, 1, 1));// 合并的单元格
		} catch (Exception e) {
		}

		FileOutputStream fos = new FileOutputStream(fileOutPath);
		wb.write(fos);// 写入
		System.out.println("写入成功");
	}

	// 创建java类
	public static void creatFile(List<String> list, String name) {
		String thisPath = path + "\\src\\main\\java\\com\\sefonsoft\\dataanalysis\\model\\";
		thisPath = thisPath + bm.toLowerCase() + "\\" + xt.toLowerCase();
		File f1 = new File(thisPath);
		if (!f1.exists()) {
			f1.mkdirs();// 创建文件夹
		}

		File file = new File(thisPath + "\\" + name + ".java");

		FileWriter fw = null;
		BufferedWriter writer = null;
		try {
			fw = new FileWriter(file);
			writer = new BufferedWriter(fw);

			for (String s : list) {
				writer.write(s);
				writer.newLine();// 换行
			}

			writer.flush();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				writer.close();
				fw.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	// 算出重复的个数
	public static int BeanNum(String name, String name2, int i) {
		try {
			Class.forName(name2);
			int beanNum = BeanNum(name, name + ++i, i);
			return beanNum;
		} catch (Exception e) {
			return i;
		}
	}

	private static void creatFileService(String tBean, String tname) throws IOException, TemplateException {
		String tb = tBean.toLowerCase();

		// 2.向root中放入模版中所需信息
		Map<String, Object> root = new HashMap<String, Object>();
		root.put("title", tBean);
		root.put("ti", tb);
		root.put("bmxt", bmxt);
		root.put("tname", tname);
		root.put("time", getNowTime());
		// 3.将模版进行指定文件的输出
		write(root);

	}

	/*
	 * 将模版进行指定文件的输出
	 */
	public static void write(Map<String, Object> root) throws IOException, TemplateException {
		String thisPath = path + "\\src\\main\\java\\com\\sefonsoft\\dataanalysis\\service\\impl\\";
		thisPath = thisPath + bm.toLowerCase() + "\\" + xt.toLowerCase();
		File f1 = new File(thisPath);
		if (!f1.exists()) {
			f1.mkdirs();// 创建文件夹
		}

		@SuppressWarnings("deprecation")
		Configuration cfg = new Configuration();
		cfg.setDirectoryForTemplateLoading(new File(path + "\\src\\test\\java\\createBean"));
		Template t = cfg.getTemplate(TEMPLATE_NAME);
		OutputStream out = new FileOutputStream(new File(thisPath + "\\" + root.get("title") + "Service.java"));
		t.process(root, new OutputStreamWriter(out));
	}

	/**
	 * 正则替换所有特殊字符
	 * 
	 * @param orgStr
	 * @return
	 */
	public static String replaceSpecStr(String orgStr) {
		if (null != orgStr && !"".equals(orgStr.trim())) {
			String regEx = "[\\s~·`!！@#￥$%^……&*（()）\\-——\\-_=+【\\[\\]】｛{}｝\\|、\\\\；;：:‘'“”\"，,《<。.》>、/？?]";
			Pattern p = Pattern.compile(regEx);
			Matcher m = p.matcher(orgStr);
			return m.replaceAll("");
		}
		return null;
	}

	/**
	 * 判断以数字开头的字符串: true:是, false:否
	 * @param str
	 * @return
	 */
	public static boolean isNumeric(String str) {
		Pattern pattern = Pattern.compile("[0-9]*");
		Matcher isNum = pattern.matcher(str.charAt(0) + "");
		if (!isNum.matches()) {
			return false;
		}
		return true;
	}
	
	/**
	 * 获取当前时间
	 * @return
	 */
	public static String getNowTime() {
		Date now = new Date(); 
		SimpleDateFormat format = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");//可以方便地修改日期格式
		String time = format.format(now);
		return time;
	}
}
